package com.kelvin.oocl.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.kelvin.oocl.dao.BookDao;
import com.kelvin.oocl.pojo.Book;
import com.kelvin.oocl.util.DBUtil;

public class BookDaoImpl implements BookDao{

	@Override
	public int addBook(Book book) {
		if(null==book){
			return 0;
		}
		Connection connection = null;
		String sql = "insert into book(id,book_name,publish_time,author,new_book,price,press) values(seq_02.nextval,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		int m = 0;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, book.getBookName());
			ps.setDate(2, new Date(book.getPublishTime().getTime()));
			ps.setString(3, book.getAuthor());
			ps.setBoolean(4, book.isNewBook());
			ps.setDouble(5, book.getPrice());
			ps.setString(6, book.getPress());
			m = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return m;
	}
	
	@Override
	public int addBooks(List<Book> books) {
		if(null==books || books.size()==0){
			return 0;
		}
		Connection connection = null;
		String sql = "insert into book(id,book_name,publish_time,author,new_book,price,press) values(seq_02.nextval,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		int counts=0;
		try {
			connection = DBUtil.getConnection();
			connection.setAutoCommit(false);
			ps = connection.prepareStatement(sql);
			for (Book book:books) {
				ps.setString(1, book.getBookName());
				ps.setDate(2, new Date(book.getPublishTime().getTime()));
				ps.setString(3, book.getAuthor());
				ps.setBoolean(4, book.isNewBook());
				ps.setDouble(5, book.getPrice());
				ps.setString(6, book.getPress());
				ps.addBatch();
				counts++;
			}
			ps.executeBatch();
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return counts;
	}

	@Override
	public int deleteBookById(Integer id) {
		if(null==id){
			return 0;
		}
		String sql = "delete from book where id=?";
		PreparedStatement ps = null;
		int m = 0;
		Connection connection = null;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
		    m= ps.executeUpdate();
		    return m;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return 0;
	}

	@Override
	public int updateBook(Book book) {
		if(null==book || book.getId()==null){
			return 0;
		}
		String sql = "update book set book_name=?,publish_time=?,author=?,new_book=?,price=?,press=? where id=?";
		PreparedStatement ps = null;
		Connection connection = null;
		int r =0;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, book.getBookName());
			ps.setDate(2, new Date(book.getPublishTime().getTime()));
			ps.setString(3, book.getAuthor());
			ps.setBoolean(4, book.isNewBook());
			ps.setDouble(5, book.getPrice());
			ps.setString(6, book.getPress());
			ps.setInt(7, book.getId());
		    r = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(null, ps, connection);
		}
		return r;
	}

	@Override
	public Book loadBookById(Integer id) {
		if(id==null){
			return null;
		}
		String sql = "select * from book where id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection connection = null;
		Book book = null;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
		    rs = ps.executeQuery();
		    while(rs.next()){
		    	book = new Book();
		    	book.setId(rs.getInt(1));
		    	book.setBookName(rs.getString(2));
		    	book.setPublishTime(new java.util.Date(rs.getDate(3).getTime()));
		    	book.setAuthor(rs.getString(4));
		    	book.setNewBook(rs.getBoolean(5));
		    	book.setPrice(rs.getDouble(6));
		    	book.setPress(rs.getString(7));
		    }
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(rs, ps, connection);
		}
		return book;
	}

	@Override
	public List<Book> findAll() {
		String sql = "select * from book";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection connection = null;
		Book book = null;
		List<Book> books = new ArrayList<Book>();
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
		    rs = ps.executeQuery();
		    while(rs.next()){
		    	book = new Book();
		    	book.setId(rs.getInt(1));
		    	book.setBookName(rs.getString(2));
		    	book.setPublishTime(new java.util.Date(rs.getDate(3).getTime()));
		    	book.setAuthor(rs.getString(4));
		    	book.setNewBook(rs.getBoolean(5));
		    	book.setPrice(rs.getDouble(6));
		    	book.setPress(rs.getString(7));
		    	books.add(book);
		    }
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.release(rs, ps, connection);
		}
		return books;
	}


}
